package it.Dao;

import it.domain.Books;
import it.domain.User;
import it.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @author 阿里木
 * @create 2023-07-1-14:44
 */
public class BooksDao {
    QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());

    //添加书籍
    public int addBooks(Books book) throws SQLException {
        String sql = "insert into books values(?,?,?,?,?,?,?,?,?)";
        int update = queryRunner.update(sql, null, book.getNumber(), book.getName(), book.getAuthor(), book.getPress(),
                book.getNum(), book.getDate(), book.getCategory(), book.getInformation());
        return update;
    }

    //总记录数 ***
    public Long totalCount(Map<String, String[]> condition) throws SQLException {
        //定义初始化模板sql
        String sql = "select count(*) from books where 1=1 ";
        StringBuilder sb = new StringBuilder(sql);
        //遍历map 拼接sql
        Set<String> keySet = condition.keySet();//map中的key
        //定义参数集合 存放value
        List<Object> params = new ArrayList<Object>();
        for (String key : keySet) {
            if ("currentPage".equals(key)) {//如果是currentpage继续遍历 否则会报错
                continue;
            }
            //获取value
            String values = condition.get(key)[0];
            String value = values.trim();
            if (value != null && !"".equals(value)) {
                if (!" ".equals(value.charAt(0))) {
                    sb.append(" and " + key + " like ? ");
                    params.add("%" + value + "%");//存放value 模糊搜索
                }

            }
        }
        sql = sb.toString();//重新赋值拼接的sql
        Long query = (Long) queryRunner.query(sql, new ScalarHandler(), params.toArray());
        //System.out.println(query);
        return query;
    }

    /**
     * 总记录数
     *
     * @return
     * @throws SQLException
     */
    public Long totalCount() throws SQLException {
        String sql = "select count(*) from books";
        Long query = (Long) queryRunner.query(sql, new ScalarHandler());
        return query;
    }

    /**
     * 条件查询
     *
     * @param query
     * @return
     * @throws SQLException
     */
    public Long queryCount(String query) throws SQLException {
        String sql = "select count(*) from books where name like ? or author like ?";
        String query1 = "%" + query + "%";
        Long count = (Long) queryRunner.query(sql, new ScalarHandler(), query1, query1);
        return count;
    }

    //查询书籍
    public List<Books> getPageData(Integer index, Integer counts, Map<String, String[]> condition) throws SQLException {
        String sql = "select * from books where 1=1 ";//小技巧
        StringBuilder sb = new StringBuilder(sql);
        //遍历map 拼接sql
        Set<String> keySet = condition.keySet();//map中的key
        //定义参数集合 存放value
        List<Object> params = new ArrayList<Object>();
        for (String key : keySet) {
            if ("currentPage".equals(key)) {//如果是currentpage继续遍历 否则会报错
                continue;
            }
            //获取value
            String values = condition.get(key)[0];//根据key获取value
            String value = values.trim();
            if (value != null && !"".equals(value)) {
                sb.append(" and " + key + " like ? ");
                params.add("%" + value + "%");//存放value 模糊搜索
            }
        }
        //添加分页查询
        sb.append(" order by id desc");//让数据按照id降序排列 当插入数据时，能够在第一页第一条显示
        sb.append(" limit ?,? ");

        //添加下标 和 每页的记录数
        params.add(index);
        params.add(counts);
        sql = sb.toString();
        System.out.println(sql);
        List<Books> booksList = queryRunner.query(sql, new BeanListHandler<Books>(Books.class), params.toArray());
        //System.out.println(booksList);
        return booksList;
    }

    /**
     * 用户分页获取list集合
     *
     * @param index
     * @param counts
     * @return
     * @throws SQLException
     */
    public List<Books> getPageList(Integer index, Integer counts) throws SQLException {
        String sql = "select * from books order by id desc limit ?,?";
        List<Books> booksList = queryRunner.query(sql, new BeanListHandler<Books>(Books.class), (index - 1) * counts, counts);
        //System.out.println(booksList);
        return booksList;
    }

    /**
     * 分页查询记录
     *
     * @param index
     * @param counts
     * @param query
     * @return
     * @throws SQLException
     */
    public List<Books> queryPageList(Integer index, Integer counts, String query) throws SQLException {
        String sql = "select * from books where name like ? or author like ? order by id desc limit ?,?";
        String query1 = "%" + query + "%";
        List<Books> booksList = queryRunner.query(sql, new BeanListHandler<Books>(Books.class), query1, query1, (index - 1) * counts, counts);
        //System.out.println(booksList);
        System.out.println(booksList);
        return booksList;
    }

    //删除书籍
    public int deleteBooks(Integer id) throws SQLException {
        String sql = "delete from books where id=?";
        int update = queryRunner.update(sql, id);
        return update;
    }

    //根据id查找书籍
    public Books findBooks(Integer id) throws SQLException {
        String sql = "select * from books where id =?";
        Books books = queryRunner.query(sql, new BeanHandler<Books>(Books.class), id);
        return books;
    }

    //根据id更新书籍
    public int modifyBooks(Books books) throws SQLException {
        String sql = "update books set number=?,name=?,author=?,press=?,num=?,date=?," +
                "category=?,information=? where id=?";
        int update = queryRunner.update(sql, books.getNumber(), books.getName(), books.getAuthor(),
                books.getPress(), books.getNum(), books.getDate(),
                books.getCategory(), books.getInformation(), books.getId());
        return update;
    }

    /**
     * 查询用户记录数
     *
     * @return
     * @throws SQLException
     */
    public Long queryUserTotalCount() throws SQLException {
        String sql = "select count(*) from user";
        Long query = (Long) queryRunner.query(sql, new ScalarHandler());
        return query;
    }

    /**
     * 显示用户信息
     *
     * @return
     * @throws SQLException
     */
    public List<User> diplayUserInfo(Integer currentPage, Integer counts) throws SQLException {
        String sql = "select * from user order by id desc limit ?,?";
        List<User> query = queryRunner.query(sql, new BeanListHandler<User>(User.class), (currentPage - 1) * counts, counts);
        return query;
    }

    /**
     * 根据用户名查找用户
     *
     * @param query
     * @param currentPage
     * @param counts
     * @return
     * @throws SQLException
     */
    public List<User> queryUser(String query, Integer currentPage, Integer counts) throws SQLException {
        String sql = "select * from user where username like ? order by id desc limit ?,?";
        String query1 = "%" + query + "%";
        List<User> user = queryRunner.query(sql, new BeanListHandler<User>(User.class), query1, (currentPage - 1) * counts, counts);
        return user;
    }

    /**
     * 查询用户总记录数
     *
     * @param query
     */
    public Long queryUserCount(String query) throws SQLException {
        String sql = "select count(*) from user where username like ?";
        String query1 = "%" + query + "%";
        Long res = (Long) queryRunner.query(sql, new ScalarHandler(), query1);
        return res;
    }

    /**
     * 添加用户
     *
     * @param user
     * @return
     * @throws SQLException
     */
    public int addUser(User user) throws SQLException {
        String sql = "insert into user values(?,?,?,?,?,?)";
        int update = queryRunner.update(sql, null, user.getUsername(), user.getPassword(), user.getName(), user.getEmail(), user.getPhone());
        return update;
    }

    /**
     * 根据id删除用户
     *
     * @param id
     * @return
     * @throws SQLException
     */
    public int deleteUser(String id) throws SQLException {
        String sql = "delete from user where id=?";
        int update = queryRunner.update(sql, id);
        return update;
    }

    /**
     * 根据id查找用户
     *
     * @param id
     * @return
     * @throws SQLException
     */
    public User getUserById(Integer id) throws SQLException {
        String sql = "select * from user where id=?";
        User user = queryRunner.query(sql, new BeanHandler<User>(User.class), id);
        return user;
    }

    /**
     * 更新用户信息
     *
     * @param user
     * @return
     * @throws SQLException
     */
    public int modifyUserInfo(User user) throws SQLException {
        String sql = "update user set username=?,password=?,name=?,email=?,phone=? where id=?";
        int update = queryRunner.update(sql, user.getUsername(), user.getPassword(), user.getName(), user.getEmail(), user.getPhone(), user.getId());
        return update;
    }
}
